/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.entities.UserGroups;
import com.ntb.common.DatabaseConnection;
import com.ntb.models.UserGroupsDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author DATBP
 */
public class UserGroupsDAOImpl extends DatabaseConnection implements UserGroupsDAO {

    public Iterator<UserGroups> getList() {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("select * from UserGroups");
            while (rs.next()) {
                UserGroups userGroup = new UserGroups();
                userGroup.setGroupID(rs.getInt("GroupID"));
                userGroup.setGroupName(rs.getString("GroupName"));
                arr.add(userGroup);
            }
            closeConnection();
            return arr.iterator();
        } catch (SQLException ex) {
            Logger.getLogger(UserGroupsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public UserGroups getRow(int id) {
        try {
            UserGroups userGroup = new UserGroups();
            openConnection();
            PreparedStatement pst = conn.prepareStatement("select * from UserGroups where GroupID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                userGroup.setGroupID(rs.getInt("GroupID"));
                userGroup.setGroupName(rs.getString("GroupName"));
                closeConnection();
                return userGroup;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserGroupsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int add(UserGroups object) {
        try {
            openConnection();
            String sql = "insert UserGroups values(?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, object.getGroupName());
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UserGroupsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int update(UserGroups object) {
        try {
            openConnection();
            String sql = "update UserGroups set GroupName=?" + " where GroupID=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, object.getGroupName());
            pst.setInt(2, object.getGroupID());
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UserGroupsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            String sql = "delete UserGroups WHERE GroupID = ? " +
                    "AND ? NOT IN (SELECT GroupID FROM Users)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            pst.setInt(2, id);
            int rs = pst.executeUpdate();
            closeConnection();
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UserGroupsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
}
